package com.epam.web_project.bank.dao.customer_dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.epam.web_project.bank.dao.factory.MySQLDAOFactory;
import com.epam.web_project.bank.entity.Account;
import com.epam.web_project.bank.entity.Card;
import com.epam.web_project.bank.entity.Customer;
import com.epam.web_project.bank.exception.UnimplemetedException;
import com.epam.web_project.bank.util.CurrencyStringConverter;

public class MySQLCustomerDAO implements CustomerDAO {

	private Connection conn = null;
	PreparedStatement ps = null;
	ResultSet resultSet = null;
	private static Logger logger = Logger.getLogger(MySQLCustomerDAO.class);
	private final String USER_AUTH_QUERY = "select * from users where login=?"
			+ " and password=?;";
	private final String INSERT_USER_QUERY = "insert users(first_name,"
			+ " last_name,dob, address, email, gender, passp_num, iin,"
			+ " telephone, login, password, question, answer) "
			+ "values (?,?,?,?,?,?,?,?,?,?,?,?,?);";
	private final String FIND_BY_LOGIN_QUERY = "select a.id as accId, u.* "
			+ "from users u inner join customer_account ca on ca.cust_id=u.id"
			+ " inner join accounts a on ca.account_id=a.id where u.login=?;";
	private final String FIND_ID_BY_LOGIN_QUERY = "select id from users "
			+ "where login=?;";
	private final String UPDATE_PASSWORD_QUERY = "update users set password=?"
			+ " where id=?;";
	private final String FIND_ANSWER_QUERY = "select answer from"
			+ " users where id=?;";
	private final String FIND_BY_UNAPPROVED_ACC_QUERY = "select u.first_name,"
			+ "u.last_name, u.registered_on,a.id,"
			+ "a.registration_date,c.type,a.currency from users u inner join"
			+ " customer_account ca on ca.cust_id=u.id inner join accounts a"
			+ " on ca.account_id=a.id inner join cards c on c.id=a.card_id "
			+ "where a.on_approval='1' and a.deleted='0';";
	private final String FIND_UNAPPROVED_CUSTOMERS_QUERY = "select u.id,"
			+ "u.first_name,u.last_name,u.registered_on,u.telephone,u.email"
			+ " from users u where u.on_approval=1 and u.deleted='0';";
	private final String FIND_BY_ID_QUERY = "select u.id,u.first_name,u.last_name,"
			+ "u.dob, u.address,u.email,u.gender,u.passp_num,u.iin,u.telephone"
			+ ",u.login,u.registered_on from users u where id=?;";
	private final String APPROVE_QUERY = "update users u set u.on_approval='0' "
			+ "where u.id=?;";
	private String FIND_BY_APPROVED_ACCOUNT_QUERY = "select a.id, "
			+ "u.first_name,u.last_name,a.iban,c.type, a.amount,a.currency,"
			+ "a.credit_lim,a.blocked from accounts a inner join "
			+ "customer_account ca on ca.account_id=a.id inner join users u"
			+ " on ca.cust_id=u.id inner join cards c on c.id=a.card_id "
			+ "where a.on_approval='0' and a.deleted='0' order by u.id;";
	private final String MARK_DELETED_QUERY = "update users set deleted='1' "
			+ "where id=?;";

	public MySQLCustomerDAO() {

	}

	public Customer userAuth(String login, String password) {
		Customer customer = null;
		try {
		conn = MySQLDAOFactory.takeConnection();
			if (conn != null && !conn.isClosed()) {
				ps = conn.prepareStatement(USER_AUTH_QUERY);
				ps.setString(1, login);
				ps.setString(2, password);
				resultSet = ps.executeQuery();
				if (resultSet.next()) {
					customer = new Customer();
					customer.setAddress(resultSet.getString("address"));
					customer.setAnswer(resultSet.getString("answer"));
					customer.setDateOfBirth(resultSet.getDate("dob"));
					customer.setId(resultSet.getInt("id"));
					customer.setFirstName(resultSet.getString("first_name"));
					customer.setLastName(resultSet.getString("last_name"));
					customer.setOnApproval(resultSet.getBoolean("on_approval"));
					customer.setPassword(resultSet.getString("password"));
					customer.setQuestion(resultSet.getString("question"));
					customer.setRole(resultSet.getString("role"));
					customer.setTelephone(resultSet.getString("telephone"));
				}
			}
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			try {
				if (conn != null && !conn.isClosed()) {
					MySQLDAOFactory.returnConnection(conn);
				}
				if (ps != null) {
					ps.close();
				}
				if (resultSet != null) {
					resultSet.close();
				}
			} catch (SQLException e) {
				logger.error(e);
			}
		}
		return customer;
	}

	@Override
	public int save(Customer customer) {
		conn = MySQLDAOFactory.takeConnection();
		int res = -1;
		try {
			if (conn != null && !conn.isClosed()) {
				conn.setAutoCommit(false);
				ps = conn.prepareStatement(INSERT_USER_QUERY);
				ps.setString(1, customer.getFirstName());
				ps.setString(2, customer.getLastName());
				ps.setDate(3, customer.getDateOfBirth());
				ps.setString(4, customer.getAddress());
				ps.setString(5, customer.getEmail());
				ps.setString(6, customer.getGender());
				ps.setString(7, customer.getPassportNumber());
				ps.setString(8, customer.getIin());
				ps.setString(9, customer.getTelephone());
				ps.setString(10, customer.getLogin());
				ps.setString(11, customer.getPassword());
				ps.setString(12, customer.getQuestion());
				ps.setString(13, customer.getAnswer());
				if (ps.executeUpdate() == 1) {
					conn.commit();
					PreparedStatement psFindId = conn
							.prepareStatement(FIND_ID_BY_LOGIN_QUERY);
					psFindId.setString(1, customer.getLogin());
					ResultSet rsFind = psFindId.executeQuery();
					if (rsFind.next()) {
						res = rsFind.getInt("id");
					}
				} else {
					conn.rollback();
				}
			}
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			try {
				if (conn != null && !conn.isClosed()) {
					MySQLDAOFactory.returnConnection(conn);
				}
				if (ps != null) {
					ps.close();
				}
			} catch (SQLException e) {
				logger.error(e);
			}
		}
		return res;
	}

	@Override
	public boolean save(List<Customer> clients) throws UnimplemetedException {
		throw new UnimplemetedException();
	}

	@Override
	public boolean update(Customer customer) throws UnimplemetedException {
		throw new UnimplemetedException();
	}

	@Override
	public boolean markDeleted(Customer customer) {
		conn = MySQLDAOFactory.takeConnection();
		try {
			if (conn != null && !conn.isClosed()) {
				conn.setAutoCommit(false);
				ps = conn.prepareStatement(MARK_DELETED_QUERY);
				ps.setInt(1, customer.getId());
				if (ps.executeUpdate() == 1) {
					conn.commit();
					return true;
				} else {
					conn.rollback();
					return false;
				}
			} else {
				return false;
			}
		} catch (SQLException e) {
			logger.error(e);
			return false;
		} finally {
			try {
				if (conn != null && !conn.isClosed()) {
					MySQLDAOFactory.returnConnection(conn);
				}
				if (ps != null) {
					ps.close();
				}
				if (resultSet != null) {
					resultSet.close();
				}
			} catch (SQLException e) {
				logger.error(e);
			}
		}
	}

	@Override
	public boolean delete(Customer customer) throws UnimplemetedException {
		throw new UnimplemetedException();
	}

	@Override
	public Customer findById(int id) {
		Customer customer = null;
		conn = MySQLDAOFactory.takeConnection();
		try {
			if (conn != null && !conn.isClosed()) {
				ps = conn.prepareStatement(FIND_BY_ID_QUERY);
				ps.setInt(1, id);
				resultSet = ps.executeQuery();
				if (resultSet.next()) {
					customer = new Customer();
					customer.setAddress(resultSet.getString("address"));
					customer.setDateOfBirth(resultSet.getDate("dob"));
					customer.setEmail(resultSet.getString("email"));
					customer.setFirstName(resultSet.getString("first_name"));
					customer.setGender(resultSet.getString("gender"));
					customer.setId(resultSet.getInt("id"));
					customer.setIin(resultSet.getString("iin"));
					customer.setLastName(resultSet.getString("last_name"));
					customer.setLogin(resultSet.getString("login"));
					customer.setPassportNumber(resultSet.getString("passp_num"));
					customer.setRegisteredOn(resultSet.getDate("registered_on"));
					customer.setTelephone(resultSet.getString("telephone"));
				}
			}
		} catch (SQLException e) {
			logger.error(e);
			return customer;
		} finally {
			try {
				if (conn != null && !conn.isClosed()) {
					MySQLDAOFactory.returnConnection(conn);
				}
				if (ps != null) {
					ps.close();
				}
				if (resultSet != null) {
					resultSet.close();
				}
			} catch (SQLException e) {
				logger.error(e);
			}
		}
		return customer;
	}

	@Override
	public Customer findByName(String nameFragment)
			throws UnimplemetedException {
		throw new UnimplemetedException();
	}

	@Override
	public Customer findByLogin(String login) {
		Customer customer = new Customer();
		conn = MySQLDAOFactory.takeConnection();
		try {
			if (conn != null && !conn.isClosed()) {
				ps = conn.prepareStatement(FIND_BY_LOGIN_QUERY);
				ps.setString(1, login);
				resultSet = ps.executeQuery();
				while (resultSet.next()) {
					customer.setId(resultSet.getInt("id"));
					customer.setFirstName(resultSet.getString("first_name"));
					customer.setLastName(resultSet.getString("last_name"));
					customer.setRole(resultSet.getString("role"));
					customer.setOnApproval(resultSet.getBoolean("on_approval"));
					customer.setQuestion(resultSet.getString("question"));
					Account account = new Account();
					account.setId(resultSet.getInt("accId"));
					customer.addAccount(account);
				}
			}
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			try {
				if (conn != null && !conn.isClosed()) {
					MySQLDAOFactory.returnConnection(conn);
				}
				if (ps != null) {
					ps.close();
				}
				if (resultSet != null) {
					resultSet.close();
				}
			} catch (SQLException e) {
				logger.error(e);
			}
		}
		return customer;
	}

	@Override
	public boolean checkSecret(Customer customer, String answer) {
		boolean res = false;
		Connection conn;
		conn = MySQLDAOFactory.takeConnection();
		try {
			if (conn != null && !conn.isClosed()) {
				ps = conn.prepareStatement(FIND_ANSWER_QUERY);
				ps.setInt(1, customer.getId());
				resultSet = ps.executeQuery();
				if (resultSet.next()) {
					String dbAnswer = resultSet.getString("answer");
					if (dbAnswer.equals(answer)) {
						res = true;
					}
				}
			}
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			try {
				if (conn != null && !conn.isClosed()) {
					MySQLDAOFactory.returnConnection(conn);
				}
				if (resultSet != null) {
					resultSet.close();
				}
				if (ps != null) {
					ps.close();
				}
			} catch (SQLException e) {
				logger.error(e);
			}
		}
		return res;
	}

	@Override
	public boolean changePassword(Customer customer, String passwordHash) {
		boolean res = false;
		conn = MySQLDAOFactory.takeConnection();
		try {
			if (conn != null && !conn.isClosed()) {
				conn.setAutoCommit(false);
				ps = conn.prepareStatement(UPDATE_PASSWORD_QUERY);
				ps.setString(1, passwordHash);
				ps.setInt(2, customer.getId());
				if (ps.executeUpdate() == 1) {
					conn.commit();
					res = true;
				} else {
					conn.rollback();
					res = false;
				}
			}
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			try {
				if (conn != null && !conn.isClosed()) {
					MySQLDAOFactory.returnConnection(conn);
				}
				if (ps != null) {
					ps.close();
				}
			} catch (SQLException e) {
				logger.error(e);
			}
		}
		return res;

	}

	@Override
	public List<Customer> findByUnapprovedAcc() {
		List<Customer> customers = new ArrayList<Customer>();
		conn = MySQLDAOFactory.takeConnection();
		
		try {
			if (conn != null && !conn.isClosed()) {
				ps = conn.prepareStatement(FIND_BY_UNAPPROVED_ACC_QUERY);
				resultSet = ps.executeQuery();
				while (resultSet.next()) {
					Customer customer = new Customer();
					customer.setFirstName(resultSet.getString("first_name"));
					customer.setLastName(resultSet.getString("last_name"));
					customer.setRegisteredOn(resultSet.getDate("registered_on"));
					Account account = new Account();
					Card card = new Card();
					card.setCardType(resultSet.getString("type"));
					account.setCard(card);
					account.setRegistered(resultSet
							.getDate("registration_date"));
					account.setCurrency(CurrencyStringConverter
							.convertToCurrency(resultSet.getString("currency")));
					account.setId(resultSet.getInt("id"));
					customer.addAccount(account);
					customers.add(customer);
				}
			}
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			try {
				if (conn != null && !conn.isClosed()) {
					MySQLDAOFactory.returnConnection(conn);
				}
				if (ps != null) {
					ps.close();
				}
				if (resultSet != null) {
					resultSet.close();
				}
			} catch (SQLException e) {
				logger.error(e);
			}
		}
		return customers;
	}

	@Override
	public List<Customer> findUnapprovedCustomers() {
		List<Customer> customers = new ArrayList<Customer>();
		conn = MySQLDAOFactory.takeConnection();
		
		try {
			if (conn != null && !conn.isClosed()) {
				ps = conn.prepareStatement(FIND_UNAPPROVED_CUSTOMERS_QUERY);
				resultSet = ps.executeQuery();
				while (resultSet.next()) {
					Customer customer = new Customer();
					customer.setId(resultSet.getInt("id"));
					customer.setFirstName(resultSet.getString("first_name"));
					customer.setLastName(resultSet.getString("last_name"));
					customer.setRegisteredOn(resultSet.getDate("registered_on"));
					customer.setTelephone(resultSet.getString("telephone"));
					customer.setEmail(resultSet.getString("email"));
					customers.add(customer);
				}
			}
		} catch (SQLException e) {
			logger.error(e);
			return new ArrayList<Customer>();
		} finally {
			try {
				if (conn != null && !conn.isClosed()) {
					MySQLDAOFactory.returnConnection(conn);
				}
				if (ps != null) {
					ps.close();
				}
				if (resultSet != null) {
					resultSet.close();
				}
			} catch (SQLException e) {
				logger.error(e);
			}
		}
		return customers;
	}

	@Override
	public boolean approve(Customer customer) {
		conn = MySQLDAOFactory.takeConnection();
		try {
			if (conn != null && !conn.isClosed()) {
				conn.setAutoCommit(false);
				ps = conn.prepareStatement(APPROVE_QUERY);
				ps.setInt(1, customer.getId());
				if (ps.executeUpdate() == 1) {
					conn.commit();
					return true;
				} else {
					conn.rollback();
					return false;
				}
			} else {
				return false;
			}
		} catch (SQLException e) {
			logger.error(e);
			return false;
		} finally {
			try {
				if (conn != null && !conn.isClosed()) {
					MySQLDAOFactory.returnConnection(conn);
				}
				if (ps != null) {
					ps.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				return false;
			}
		}
	}

	@Override
	public List<Customer> findByApprovedAcc() {
		List<Customer> customers = new ArrayList<Customer>();
		conn = MySQLDAOFactory.takeConnection();
		try {
			if (conn != null && !conn.isClosed()) {
				ps = conn.prepareStatement(FIND_BY_APPROVED_ACCOUNT_QUERY);
				resultSet = ps.executeQuery();
				while (resultSet.next()) {
					Customer customer = new Customer();
					Account account = new Account();
					Card card = new Card();
					account.setIban(resultSet.getString("iban"));
					account.setAmount(resultSet.getBigDecimal("amount"));
					account.setCurrency(CurrencyStringConverter
							.convertToCurrency(resultSet.getString("currency")));
					account.setCreditLimit(resultSet
							.getBigDecimal("credit_lim"));
					account.setBlocked(resultSet.getBoolean("blocked"));
					account.setId(resultSet.getInt("id"));
					card.setCardType(resultSet.getString("type"));
					account.setCard(card);
					customer.addAccount(account);
					customer.setFirstName(resultSet.getString("first_name"));
					customer.setLastName(resultSet.getString("last_name"));
					customers.add(customer);
				}
			}
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			try {
				if (conn != null && !conn.isClosed()) {
					MySQLDAOFactory.returnConnection(conn);
				}
				if (ps != null) {
					ps.close();
				}
				if (resultSet != null) {
					resultSet.close();
				}
			} catch (SQLException e) {
				logger.error(e);
			}
		}
		return customers;
	}

}
